package net.dfrz.dao.impl;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.Date;
import java.util.List;

import net.dfrz.dao.UserDao;
import net.dfrz.domain.User;
import net.dfrz.util.JdbcUtils;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.junit.Test;

public class UserDaoDBImpl implements UserDao {
	@Override
	public void add(User user) {
		try {
			Connection conn = JdbcUtils.getConnection();
			QueryRunner runner = new QueryRunner();
			String sql = "insert into user_info (id,username,password,age,is_del,mail,tel,sex,islock,zip,address,name) "
					+ " values(?,?,?,?,?,?,?,?,?,?,?,?)";

			Object params[] = { user.getId(), user.getUsername(),user.getPassword(), 
					user.getAge(),user.getIs_del(), user.getMail(),
					user.getTel(), user.getSex(), user.getIslock(),
					user.getZip(), user.getAddress(),user.getName() };
			runner.update(conn,sql, params);
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}
	@Override
	public User load(String id) {
		try {
			Connection conn = JdbcUtils.getConnection();
			QueryRunner runner = new QueryRunner();
			String sql = "select * from user_info where id = ? and is_del = 0";
			return (User) runner.query(conn,sql, id, new BeanHandler(User.class));
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}
	
	public User getUserByUsernameAndPassword(String username ,String password) {
		try {
			Connection conn = JdbcUtils.getConnection();
			QueryRunner runner = new QueryRunner();
			String sql = "select * from user_info where username = ? and password = ? and is_del = 0";
			Object[] params ={username,password};
			return (User) runner.query(conn,sql, params ,new BeanHandler(User.class));
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}
	
	@Override
	public User loadByName(String username) {
		try {
			Connection conn = JdbcUtils.getConnection();
			QueryRunner runner = new QueryRunner();
			String sql = "select * from user_info where username = ? and is_del = 0";
			return (User) runner.query(conn,sql, username, new BeanHandler(User.class));
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}
	
	@Override
	public int delete(String id) {
		try {
			Connection conn = JdbcUtils.getConnection();
			QueryRunner runner = new QueryRunner();
			String sql = "update user_info set is_del = 1 where id = ?";
			return runner.update(conn,sql, id);
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}
	
	@Override
	public List<User> getAll() {
		try {
			Connection conn = JdbcUtils.getConnection();
			QueryRunner runner = new QueryRunner();
			String sql = "select * from user_info where is_del = 0";
			return (List<User>) runner.query(conn,sql,new BeanListHandler(User.class));
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}
	@Override
	public int update(User user){
		try {
			Connection conn = JdbcUtils.getConnection();
			QueryRunner runner = new QueryRunner();
			String sql = "update user_info set username=?,password=?,age=?," +
					"is_del=?,mail=?,tel=?," +
					"sex=?,islock=?,zip=?," +
					"address=?,name=? where id=? ";
			Object params[] = { user.getUsername(),
					user.getPassword(), user.getAge(),
					user.getIs_del(), user.getMail(),
					user.getTel(), user.getSex(), user.getIslock(),
					user.getZip(), user.getAddress(),user.getName(), user.getId()};
			return runner.update(conn,sql, params);
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}
	
	public int lock(String id,boolean islock)
	{
		int num = 0;
		if(islock)
		{
			num = 1;
		}
		try {
			Connection conn = JdbcUtils.getConnection();
			QueryRunner runner = new QueryRunner();
			String sql = "update user_info set islock = ? where id = ?";
			Object params[] = { num,id};
			return runner.update(conn,sql, params);
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
		
	}
	@Override
	public int delete(User u) {
		
		return 0;
	}

	@Override
	public List<User> find(String info) {
		
		return null;
	}
	@Override
	public User loadByNameandPassword(String username, String password) {
		// TODO Auto-generated method stub
		return null;
	}
}
